package com.wolfpire.workflow.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.system.model.Role;
import com.wolfpire.workflow.common.Constants;
import com.wolfpire.workflow.dao.MyTaskDao;
import com.wolfpire.workflow.em.TaskType;
import com.wolfpire.workflow.om.MyTask;


@Repository("myTaskDao")
public class MyTaskDaoImpl extends BaseHibernateDao<MyTask, Long> implements MyTaskDao{
	
	/**
	 *  根据流程实例ID查询任务列表 
	 * @param instanceId 流程实例ID
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MyTask> queryProcessTaskList(Long instanceId){
		Criteria criteria = createCriteria( Restrictions.eq("instanceId", instanceId) );
		criteria.addOrder(Order.asc("endTime"));
		return criteria.list();
	}
	
	/**
	 * 查询流程审核详情
	 * @param instanceId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryTaskList(final Long instanceId){
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T.TASK_ID, T.TASK_NAME, T1.NODE_TYPE, T.TASK_TYPE, T1.FORM_TYPE_ID, T.OPERATE_TYPE, T.TRANSACTOR_NAME, T.ORG_NAME, ");
		sql.append(" T.REMARKS, TO_CHAR( T.START_TIME, 'YYYY-MM-DD HH24:MI') AS START_TIME, ");
		sql.append(" TO_CHAR( T.START_TIME, 'YYYY-MM-DD') AS START_TIME_TEMP,TO_CHAR(T.END_TIME, 'YYYY-MM-DD') AS END_TIME_TEMP, ");
		sql.append(" TO_CHAR(T.END_TIME, 'YYYY-MM-DD HH24:MI') AS END_TIME, T.TRANSACTOR_ID,T.TRANSACTOR_NAME, ");
		sql.append(" FUN_TIME_INTERVAL(T.END_TIME, T.START_TIME) AS INTER_TIME, T.FORM_TYPE_ID, T.FORM_ID, T.NODE_ID,T.ISTIMEOUT, ");
		
		sql.append(" T.ESTIMATED_HOURS,T.COMPLETE_HOURS,TO_CHAR(T.NEXT_START_TIME, 'YYYY-MM-DD') AS NEXT_START_TIME, ");
		sql.append(" TO_CHAR(T.NEXT_COMPLETE_TIME, 'YYYY-MM-DD') AS NEXT_COMPLETE_TIME,T.ATTACHMENT_IDS,T.MESSAGE ,T.BUGTYPE ");
		
		sql.append(" FROM  WF_TASK T LEFT JOIN WF_NODE T1 ON T.NODE_ID=T1.NODE_ID WHERE T.INSTANCE_ID=? ORDER BY T.END_TIME ASC");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getBackTaskList(Long instanceId,String operateType){
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T.TASK_ID,T.TASK_NAME,T.TASK_TYPE,T.OPERATE_TYPE,T.INSTANCE_ID,T.BUGTYPE, ")
		.append(" T.TRANSACTOR_ID,TO_CHAR(T.START_TIME, 'yyyy-mm-dd') AS START_TIME,T.TRANSACTOR_NAME  ")
		.append(" FROM  WF_TASK T ")
		.append(" WHERE 1=1 AND T.INSTANCE_ID=").append(instanceId)
		.append(" AND T.OPERATE_TYPE != ").append(operateType)
		.append(" ORDER BY T.TASK_ID DESC ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
	}
	
	/**
	 * 查询驳回任务
	 * @param instanceId
	 * @return
	 */
	public MyTask getBackTask(Long instanceId){
		Criteria criteria = createCriteria( Restrictions.eq("instanceId", instanceId) );
		criteria.addOrder(Order.desc("endTime"));
		criteria.setMaxResults(1);
		return (MyTask) criteria.uniqueResult();
	}
	
	
	/**
	 * 查询会签任务历史列表
	 * @param instanceId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MyTask> querySignTaskList(Long instanceId){
		Criteria criteria = createCriteria(
			Restrictions.eq("instanceId", instanceId), 
			Restrictions.eq("taskType", TaskType.SIGN.getIndex())
		);
		
		return criteria.list();
	}
	
	/**
	 * 按formTypeId查询待办信息
	 * @param orgId
	 * @param postId
	 * @param funId
	 * @param roleList
	 * @param userId
	 * @param formTypeId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskList(Long orgId, Long postId, Long funId, List<Role> roleList, Long userId,String formTypeId){
		
		StringBuffer sql = buildTaskSql(orgId, postId, roleList, userId,Integer.valueOf(formTypeId));
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
	}
	
	/**
	 * 待办数
	 * @param orgId
	 * @param postId
	 * @param funId
	 * @param roleList
	 * @param userId
	 * @return
	 */
	public int queryMyTaskCountNum(Long orgId, Long postId, Long funId, List<Role> roleList, Long userId ,String formTypeId){
		StringBuffer sql = buildTaskCountSql(orgId, postId, roleList, userId,formTypeId);
		
		Query query = getSession().createSQLQuery(sql.toString());
		Object num = query.uniqueResult();
		return Integer.valueOf(num.toString()).intValue();
	}


	/**
	 * 构造待办SQL
	 * @param orgId
	 * @param postId
	 * @param roleList
	 * @param userId
	 * @param formTypeId
	 * @return
	 */
	public StringBuffer buildTaskSql(Long orgId, Long postId,
			List<Role> roleList, Long userId,Integer formTypeId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(");
		sql.append("SELECT T.DBID_, T.EXECUTION_, T.ACTIVITY_NAME_,T.PROCINST_,TO_CHAR(T.CREATE_, 'yyyy-mm-dd HH:mi:ss') AS CREATE_," +
				"T1.USERID_,T1.TYPE_, T2.PROC_NAME,T2.FORM_ID,T2.FORM_TYPE_ID, T2.WORKFLOW_ID," +
				" T2.USER_NAME, T2.ORG_NAME,T2.INSTANCE_ID,T2.CUR_NODE,T2.STATUS," +
				" TO_CHAR(T2.START_TIME, 'yyyy-mm-dd HH:mi:ss') AS START_TIME");
		sql.append(" FROM  JBPM4_TASK T LEFT JOIN ");
		sql.append(" JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "); 
		sql.append(" LEFT JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
		
		sql.append(" WHERE 1=2");

		
		if(orgId != null){
			sql.append(" OR (T1.USERID_='").append(orgId).append("' AND T1.TYPE_='1')");
		}
		
		if(postId != null){
			sql.append(" OR (T1.USERID_='").append(postId).append("' AND T1.TYPE_='4')");
		}
		
		if(roleList != null){
			for (Role role : roleList) {
				sql.append(" OR (T1.USERID_='").append(role.getId()).append("' AND T1.TYPE_='2')");
			}
		}
		
		if(userId != null){
			sql.append(" OR (T1.USERID_='").append(userId).append("' AND T1.TYPE_='3')");
		}
		
		sql.append(" ) TT WHERE 1=1 ");
		if(formTypeId != null){
			sql.append(" AND (TT.FORM_TYPE_ID=").append(formTypeId).append(")");
		}
		sql.append(" ORDER BY TT.CREATE_ DESC ");
		return sql;
	}

	
	/**
	 * 构造任务数据SQL
	 * @param orgId
	 * @param postId
	 * @param funId
	 * @param roleList
	 * @param userId
	 * @param eventTable
	 * @param formTypeId
	 * @return
	 */
	public StringBuffer buildTaskCountSql(Long orgId, Long postId,
			List<Role> roleList, Long userId,String formTypeId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(*) tasknum ");
		sql.append(" FROM  JBPM4_TASK T LEFT JOIN ");
		sql.append(" JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "); 
		sql.append(" LEFT JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
		
		sql.append(" WHERE 1=2");
		if(orgId != null){
			sql.append(" OR (T1.USERID_=").append(orgId).append(" AND T1.TYPE_='1')");
		}
		
		if(postId != null){
			sql.append(" OR (T1.USERID_=").append(postId).append(" AND T1.TYPE_='4')");
		}
		
		if(roleList != null){
			for (Role role : roleList) {
				sql.append(" OR (T1.USERID_=").append(role.getId()).append(" AND T1.TYPE_='2')");
			}
		}
		
		if(userId != null){
			sql.append(" OR (T1.USERID_=").append(userId).append(" AND T1.TYPE_='3')");
		}
		
		if(formTypeId != null){
			sql.append(" AND (T2.FORM_TYPE_ID=").append(formTypeId).append(")");
		}
		return sql;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskListAndEvent(Long orgId, Long postId, Long funId, List<Role> roleList, Long userId ,String eventTable,String formTypeId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.DBID_, T.EXECUTION_, T.ACTIVITY_NAME_,TO_CHAR(T.CREATE_, 'yyyy-mm-dd HH:mi:ss') AS CREATE_,T1.USERID_,T1.TYPE_, T2.PROC_NAME, T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE ");
		
		if(eventTable != null){
			sql.append(",E.TYPE_,E.CONTENT_,E.INFLUENCE_DEGREE,E.OCCURRED,E.SOURCE,E.INVOLVED_NUM ");
			sql.append(",TO_CHAR(E.HAPPEN_TIME,'yyyy-MM-dd HH24:mi:ss') HAPPEN_TIME ");
		}
		
		
		sql.append(" FROM  JBPM4_TASK T LEFT JOIN ");
		sql.append(" JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "); 
		sql.append(" LEFT JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
		sql.append("AND T2.STATUS IN('1', '2')" );// 过滤挂起流程status -3挂起
		if(eventTable != null){
			sql.append(" LEFT JOIN ").append(eventTable).append(" E ON E.EVENT_ID = T2.FORM_ID");
		}
		
		 
		sql.append(" WHERE 1=2");

		
		if(orgId != null){
			sql.append(" OR (T1.USERID_=").append(orgId).append(" AND T1.TYPE_='1')");
		}
		
		if(postId != null){
			sql.append(" OR (T1.USERID_=").append(postId).append(" AND T1.TYPE_='4')");
		}
		
		if(roleList != null){
			for (Role role : roleList) {
				sql.append(" OR (T1.USERID_=").append(role.getId()).append(" AND T1.TYPE_='2')");
			}
		}
		
		if(userId != null){
			sql.append(" OR (T1.USERID_=").append(userId).append(" AND T1.TYPE_='3')");
		}
		
		sql.append(" AND T2.EVENTOPTYPE IN ('00','01','02','04','06','11')");
		
		if(formTypeId != null){
			sql.append(" AND (T2.FORM_TYPE_ID=").append(formTypeId).append(")");
		}
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	/**
	 * 我的待办关联催办记录置顶显示
	 * @param orgId
	 * @param postId
	 * @param funId
	 * @param roleList
	 * @param userId
	 * @param eventTable
	 * @param formTypeId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskListAndEventByRemind(Long orgId, Long postId, Long funId, List<Role> roleList, Long userId ,String eventTable,String formTypeId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.DBID_, T.EXECUTION_, T.ACTIVITY_NAME_,TO_CHAR(T.CREATE_, 'yyyy-mm-dd HH:mi:ss') AS CREATE_,T1.USERID_,T1.TYPE_, T2.PROC_NAME, T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE, T4.STATUS ");
		
		if(eventTable != null){
			sql.append(",E.TYPE_,E.CONTENT_,E.INFLUENCE_DEGREE,E.OCCURRED,E.SOURCE,E.INVOLVED_NUM ");
			sql.append(",TO_CHAR(E.HAPPEN_TIME,'yyyy-MM-dd HH24:mi:ss') HAPPEN_TIME ");
		}
		
		sql.append(" FROM  JBPM4_TASK T LEFT JOIN ");
		sql.append(" JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "); 
		sql.append(" LEFT JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
		sql.append("AND T2.STATUS IN('1', '2')" );// 过滤挂起流程status -3挂起
		sql.append("  LEFT JOIN  WF_REMIND T4 ON T2.INSTANCE_ID = T4.INSTANCE_ID ");
		if(eventTable != null){
			sql.append(" LEFT JOIN ").append(eventTable).append(" E ON E.EVENT_ID = T2.FORM_ID");
		}
		
		sql.append(" WHERE 1=2");
		
		if(orgId != null){
			sql.append(" OR (T1.USERID_=").append(orgId).append(" AND T1.TYPE_='1')");
		}
		
		if(postId != null){
			sql.append(" OR (T1.USERID_=").append(postId).append(" AND T1.TYPE_='4')");
		}
		
		if(roleList != null){
			for (Role role : roleList) {
				sql.append(" OR (T1.USERID_=").append(role.getId()).append(" AND T1.TYPE_='2')");
			}
		}
		
		if(userId != null){
			sql.append(" OR (T1.USERID_=").append(userId).append(" AND T1.TYPE_='3')");
		}
		
		sql.append(" AND T2.EVENTOPTYPE IN ('00','01','02','04','06','11')");
		
		if(formTypeId != null){
			sql.append(" AND (T2.FORM_TYPE_ID=").append(formTypeId).append(")");
		}
		
		sql.append(" ORDER BY T4.REMIND_DATE ,CREATE_ ASC");
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	/**不支持有会签节点
	 * 根据instanceId获取jbpm关联数据
	 * @param instanceId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskByInstanceId( Long instanceId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct (T.DBID_),T2.INSTANCE_ID,T.TIMEOUT_, T.EXECUTION_,T.PROCINST_, T.ACTIVITY_NAME_,T.CREATE_, T2.PROC_NAME, T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE ");
		sql.append(" FROM (").append(" SELECT T5.DBID_,T5.TIMEOUT_, T5.EXECUTION_,T5.PROCINST_, T5.ACTIVITY_NAME_,TO_CHAR(T5.CREATE_, 'yyyy-mm-dd') AS CREATE_ FROM ");
		sql.append(" JBPM4_TASK T5 ").append(" WHERE 1=1 AND T5.DBVERSION_ != '0'  ");
		sql.append(" ) T ");
		sql.append(" JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
//		sql.append(" AND T2.STATUS IN('1', '2')" );// 过滤挂起流程status -3挂起
		sql.append(" WHERE T2.INSTANCE_ID = ?");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	/**只支持会签节点
	 * 根据instanceId获取jbpm关联数据
	 * @param instanceId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskByInstanceIdForSign(Long instanceId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct (T.DBID_),T2.INSTANCE_ID,T.TIMEOUT_,T.FORM_, T.EXECUTION_,T.PROCINST_, T.ASSIGNEE_,T.ACTIVITY_NAME_,T.CREATE_, T2.PROC_NAME, T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE ");
		sql.append(" FROM (").append(" SELECT T5.DBID_,T5.TIMEOUT_,T5.FORM_, T5.EXECUTION_,T5.PROCINST_,T5.ASSIGNEE_, T5.ACTIVITY_NAME_,TO_CHAR(T5.CREATE_, 'yyyy-mm-dd') AS CREATE_ FROM ");
		sql.append(" JBPM4_TASK T5 ").append(" WHERE 1=1 AND  (T5.ASSIGNEE_ IS NOT NULL )  ");
		sql.append(" ) T ");
		
		sql.append(" JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ");
//		sql.append(" AND T2.STATUS IN('1', '2')" );// 过滤挂起流程status -3挂起
		sql.append(" WHERE T2.INSTANCE_ID = ?");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	@SuppressWarnings("unchecked")
	public Map<String, Object> queryWfInstanceByformId(String formTypeId,
			String formId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  T2.INSTANCE_ID,T2.FORM_ID,T2.FORM_TYPE_ID,T2.PROC_NAME,T2.CUR_NODE,");
		sql.append("TO_CHAR(T2.START_TIME,'yyyy-MM-dd HH24:mi:ss') START_TIME," );
		sql.append("TO_CHAR(T2.END_TIME,'yyyy-MM-dd HH24:mi:ss') END_TIME,");
		sql.append(" T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE ,T2.STATUS ");
		sql.append(" FROM  WF_PROCESS_INSTANCE T2 ");
		sql.append(" WHERE  T2.FORM_TYPE_ID = ").append(formTypeId);
		sql.append(" AND T2.FORM_ID = ").append(formId);
				
		Query query = getSession().createSQLQuery(sql.toString());
		Map<String, Object> rs_map = null;
		List<Map<String, Object>> ls_map = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if(!ls_map.isEmpty()){
			rs_map = (Map<String, Object>)ls_map.get(0);
		}
		return  rs_map;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String, Object> queryProInstance(Long workFlowId, String formId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  T2.INSTANCE_ID,T2.FORM_ID,T2.FORM_TYPE_ID,T2.PROC_NAME,T2.CUR_NODE,");
		sql.append("TO_CHAR(T2.START_TIME,'yyyy-MM-dd HH24:mi:ss') START_TIME," );
		sql.append("TO_CHAR(T2.END_TIME,'yyyy-MM-dd HH24:mi:ss') END_TIME,");
		sql.append(" T2.USER_NAME, T2.ORG_NAME, T2.EVENTOPTYPE ,T2.STATUS ");
		sql.append(" FROM  WF_PROCESS_INSTANCE T2 ");
		sql.append(" WHERE  T2.WORKFLOW_ID = ").append(workFlowId);
		sql.append(" AND T2.FORM_ID = ").append(formId);
				
		Query query = getSession().createSQLQuery(sql.toString());
		Map<String, Object> rs_map = null;
		List<Map<String, Object>> ls_map = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if(!ls_map.isEmpty()){
			rs_map = (Map<String, Object>)ls_map.get(0);
		}
		return  rs_map;
	}

	@SuppressWarnings("unchecked")
	public MyTask getMyTask(String instanceId, String taskName) {

		Criteria criteria = createCriteria(
				Restrictions.eq("instanceId", Long.parseLong(instanceId)),
				Restrictions.eq("taskName", taskName)).addOrder(Order.desc("endTime"));
		List<MyTask> taskList = criteria.list();
		if (taskList.size() > 0){
			return taskList.get(0);
		}
		return null;
	}

	
	@SuppressWarnings("unchecked")
	public Map<String, Object> queryMyTask(String instanceId, String taskName) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.TASK_ID,T.TASK_NAME,T.NODE_ID,T.TASK_TYPE,T.OPERATE_TYPE,T.INSTANCE_ID,T.TRANSACTOR_ID,")
		.append(" T.TRANSACTOR_NAME,T.ORG_NAME,T.REMARKS,TO_CHAR(T.START_TIME, 'yyyy-mm-dd HH24:mi:ss') AS START_TIME,")
		.append(" TO_CHAR(T.END_TIME, 'yyyy-mm-dd HH24:mi:ss') AS END_TIME FROM WF_TASK T WHERE T.INSTANCE_ID= ").append(instanceId);
		sql.append(" AND T.TASK_NAME = ").append("'"+taskName+"' ");
		Query query = getSession().createSQLQuery(sql.toString());
		
		Map<String, Object> rs_map = null;
		List<Map<String, Object>> ls_map = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
		if(!ls_map.isEmpty()){
			rs_map = (Map<String, Object>)ls_map.get(0);
			String tempUserName = "";
			for(Map<String, Object> tempMyTask:ls_map){
				if(tempMyTask.get("TRANSACTOR_NAME")!=null){
					if(tempUserName.indexOf((String)tempMyTask.get("TRANSACTOR_NAME")) >= 0){
						tempUserName +=  tempMyTask.get("TRANSACTOR_NAME") + ",";
					}
				}
			}
			if(!tempUserName.equals("")){
				tempUserName = tempUserName.substring(0,tempUserName.length()-1);
				rs_map.put("TRANSACTOR_NAME", tempUserName);
			}
			String remarks = (String) ls_map.get(0).get("REMARKS");
			if(remarks==null){
				rs_map.put("REMARKS", "");
			}
			if(null!=ls_map.get(0).get("OPERATE_TYPE")){
				String operateType = ((Character) ls_map.get(0).get("OPERATE_TYPE")).toString();
				rs_map.put("OPERATE_TYPE", com.wolfpire.workflow.em.OperateType.getNameByIndex(operateType));
			}
			
		}
		
		return  rs_map;
	}

	/**
	 * 根据实例查当前的任务
	 * @param instanceId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryActTaskByInstanceId(Long instanceId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM JBPM4_TASK T WHERE T.PROCINST_= ").append(instanceId);

		Query query = getSession().createSQLQuery(sql.toString());
		
		return  query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	
	public int getAfterTaskCountByTaskName(String taskName,Long instanceId,Long taskId){
		String sql = "select * from wf_task t where t.end_time > (select max(end_time) from wf_task where task_name = ? and instance_id=?) and t.instance_id=? and t.task_id > ?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskName);
		query.setParameter(1, instanceId);
		query.setParameter(2, instanceId);
		query.setParameter(3, taskId);
		return query.list().size();
	}
	
	@SuppressWarnings("unchecked")
	public List<MyTask> queryHistoryTaskList(Long userId) {
		
		Criteria criteria = createCriteria(Restrictions.eq("transactorId", userId));
		return criteria.list();
		
	}
	
	public void modifyTaskWarn(String taskId){
		String sql = "UPDATE JBPM4_TASK T SET T.TIMEOUT_='"+Constants.WARN+"' WHERE T.DBID_=?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		query.executeUpdate();
	}
	
	public void modifyTaskTimeout(String taskId){
		String sql = "UPDATE JBPM4_TASK T SET T.TIMEOUT_='"+Constants.TIMEOUT+"' WHERE T.DBID_=?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		query.executeUpdate();
	}
	public void modifyTaskAlert(String taskId){
		String sql = "UPDATE JBPM4_TASK T SET T.TIMEOUT_='"+Constants.ALERT+"' WHERE T.DBID_=?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		query.executeUpdate();
	}
	
	public int countTaskByTimeout(String timeout) {

		String sql = "SELECT COUNT(*) taskunm FROM ( "
				+ "SELECT distinct (T.DBID_), T2.INSTANCE_ID, T.EXECUTION_ ,TO_CHAR(T.CREATE_, 'yyyy-mm-dd HH:mi:ss') AS CREATE_,"
				+ " T.ACTIVITY_NAME_,T.TIMEOUT_, T2.PROC_NAME, T2.STATUS, R.STATUS AS STATUS_"
				+ " FROM " + Constants.WORKFLOW + ".JBPM4_TASK T LEFT JOIN "
				+ Constants.WORKFLOW
				+ ".JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ " + " JOIN "
				+ Constants.WORKFLOW
				+ ".WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID ";

		sql += " LEFT JOIN " + Constants.WORKFLOW + ".WF_REMIND R "
				+ " ON R.TASK_ID = T.DBID_ "
				+ " WHERE T.TIMEOUT_=? )";

		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, timeout);
		Object num = query.uniqueResult();
		return Integer.valueOf(num.toString()).intValue();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryTaskListByTimeout(){
		String sql = "SELECT T.DBID_,T.NAME_, T.EXECUTION_,T.EXECUTION_ID_,T.CREATE_ FROM JBPM4_TASK T WHERE T.TIMEOUT_ IS NULL";
		Query query = getSession().createSQLQuery(sql);
		
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTaskByTaskId(Long taskId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.TASK_ID, T.TASK_NAME, T.TASK_TYPE, T.OPERATE_TYPE, T.TRANSACTOR_NAME, T.ORG_NAME, ");
		sql.append("T.REMARKS, TO_CHAR( T.START_TIME, 'YYYY-MM-DD HH:MI:SS') AS START_TIME, ");
		sql.append("TO_CHAR(T.END_TIME, 'YYYY-MM-DD HH:MI:SS') AS END_TIME, ");
		sql.append("FUN_TIME_INTERVAL(T.END_TIME, T.START_TIME) AS INTER_TIME, T.FORM_TYPE_ID, T.FORM_ID, T.NODE_ID ");
		sql.append(" FROM  WF_TASK T WHERE T.TASK_ID=? ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, taskId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	public MyTask queryLastTaskInfo(Long instanceId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM wf_task wt WHERE wt.INSTANCE_ID=? ORDER BY wt.task_id DESC LIMIT 1 ");
		SQLQuery query = (SQLQuery) this.createSqlQuery(sql.toString(), new Object[]{instanceId});
		query.addEntity(MyTask.class);		
		return  (MyTask) query.uniqueResult();
	} 
	
	@Override
	public MyTask findFirstTaskInfo(Long instanceId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM wf_task wt WHERE wt.INSTANCE_ID=? ORDER BY wt.task_id ASC LIMIT 1 ");
		SQLQuery query = (SQLQuery) this.createSqlQuery(sql.toString(), new Object[]{instanceId});
		query.addEntity(MyTask.class);		
		return  (MyTask) query.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskBeforeCur(Long instanceId,Long taskId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.TASK_ID, T.TASK_NAME, T.TASK_TYPE, T.OPERATE_TYPE, T.TRANSACTOR_NAME, T.ORG_NAME, ");
		sql.append("T.REMARKS, TO_CHAR( T.START_TIME, 'YYYY-MM-DD HH:MI:SS') AS START_TIME, ");
		sql.append("TO_CHAR(T.END_TIME, 'YYYY-MM-DD HH:MI:SS') AS END_TIME,T.TRANSACTOR_ID, ");
		sql.append("FUN_TIME_INTERVAL(T.END_TIME, T.START_TIME) AS INTER_TIME, T.FORM_TYPE_ID, T.FORM_ID, T.NODE_ID ");
		sql.append(" FROM  WF_TASK T WHERE T.INSTANCE_ID=? ");
		sql.append(" AND T.OPERATE_TYPE ='1' ");
		sql.append(" AND T.TASK_ID < ").append(taskId);
		sql.append(" ORDER BY T.TASK_ID DESC ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMyTaskPerson(Long instanceId,String taskName){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.TASK_ID, T.TASK_NAME, T.TASK_TYPE, T.OPERATE_TYPE, T.TRANSACTOR_NAME, T.ORG_NAME, ");
		sql.append("T.REMARKS, TO_CHAR( T.START_TIME, 'YYYY-MM-DD HH:MI:SS') AS START_TIME, ");
		sql.append("TO_CHAR(T.END_TIME, 'YYYY-MM-DD HH:MI:SS') AS END_TIME,T.TRANSACTOR_ID, ");
		sql.append("FUN_TIME_INTERVAL(T.END_TIME, T.START_TIME) AS INTER_TIME, T.FORM_TYPE_ID, T.FORM_ID, T.NODE_ID ");
		sql.append(" FROM  WF_TASK T WHERE T.INSTANCE_ID=? ");
		sql.append(" AND T.OPERATE_TYPE ='1' ");
		sql.append(" AND T.TASK_NAME= '").append(taskName).append("'");
		
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryFile(Long instanceId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.TASK_ID, T.TASK_NAME, T.TASK_TYPE, T.OPERATE_TYPE, T.TRANSACTOR_NAME, T.ORG_NAME, ");
		sql.append("T.REMARKS, TO_CHAR( T.START_TIME, 'YYYY-MM-DD HH:MI:SS') AS START_TIME, ");
		sql.append("TO_CHAR(T.END_TIME, 'YYYY-MM-DD HH:MI:SS') AS END_TIME,T.TRANSACTOR_ID, ");
		sql.append("FUN_TIME_INTERVAL(T.END_TIME, T.START_TIME) AS INTER_TIME, T.FORM_TYPE_ID, T.FORM_ID, T.NODE_ID, ");
		sql.append(" T1.ATTACHMENT_ID,T1.FILE_NAME,T1.FILE_PATH,T1.FILE_SIZE ");
		
		sql.append(" FROM  WF_TASK T ");
		sql.append(" left join  T_SM_ATTACHMENT T1 on t.TASK_ID=t1.BIZ_ID ");
		
		sql.append(" WHERE T.INSTANCE_ID=? and t1.ATTACHMENT_TYPE ='wf_taskId' ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	public int getParticipationCountByTaskId(String taskId, String userId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(*) FROM JBPM4_PARTICIPATION P ")
			.append(" WHERE P.TASK_=? AND P.TYPE_='3' AND P.USERID_=? ");
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, taskId);
		query.setParameter(1, userId);
		return ((BigDecimal) query.uniqueResult()).intValue();
	}

	@SuppressWarnings("unchecked")
	public Map<String, Object> getDemandTestInfo(String formId) {
		Map<String, Object> rs_map = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT WT_.TASK_NAME,WT_.TRANSACTOR_NAME, ")
				.append(" TO_CHAR(WT_.START_TIME,'YYYY-MM-DD HH:MI:SS') AS START_TIME_, ")
				.append(" TO_CHAR(WT_.END_TIME,'YYYY-MM-DD HH:MI:SS') AS END_TIME_,WT_.REMARKS ")
				.append(" FROM WF_PROCESS_INSTANCE I ")
				.append(" LEFT JOIN WF_TASK WT_ ")
				.append(" ON I.INSTANCE_ID = WT_.INSTANCE_ID ")
				.append(" WHERE I.TASK_ID = (SELECT MAX(WT.TASK_ID) ")
				.append(" FROM WF_PROCESS_INSTANCE I ")
				.append(" LEFT JOIN WF_TASK WT ")
				.append(" ON I.INSTANCE_ID = WT.INSTANCE_ID ")
				.append(" WHERE I.FORM_ID='").append(formId).append("' ")
				.append(" AND WT.TASK_NAME='测试子流程') ")
				.append(" AND I.PARENT_ID IS NOT NULL ")
				.append(" AND WT_.TASK_NAME='测试' ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		List<Map<String, Object>> ls_maps = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if (!ls_maps.isEmpty()) {
			rs_map = ls_maps.get(0);
		}
		return rs_map;
	}

	@SuppressWarnings("unchecked")
	public Map<String, Object> getBugsTestInfo(String formId) {
		Map<String, Object> rs_map = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT WT.TASK_NAME,WT.TRANSACTOR_NAME, ")
				.append(" TO_CHAR(WT.START_TIME,'YYYY-MM-DD HH:MI:SS') AS START_TIME_, ")
				.append(" TO_CHAR(WT.END_TIME,'YYYY-MM-DD HH:MI:SS') AS END_TIME_,WT.REMARKS ")
				.append(" FROM WF_TASK WT ")
				.append(" WHERE WT.TASK_ID= (  ")
				.append(" SELECT MAX(WT.TASK_ID) ")
				.append(" FROM WF_PROCESS_INSTANCE I ")
				.append(" LEFT JOIN WF_TASK WT ")
				.append(" ON I.INSTANCE_ID = WT.INSTANCE_ID ")
				.append(" WHERE I.FORM_ID='").append(formId).append("' ")
				.append(" AND WT.TASK_NAME='回归测试' ) ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		List<Map<String, Object>> ls_maps = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if (!ls_maps.isEmpty()) {
			rs_map = ls_maps.get(0);
		}
		return rs_map;
	}
	
	@SuppressWarnings("unchecked")
	public List<BigDecimal> queryTaskIdByFormIdAndName(String formIds, String taskName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT max(WT.TASK_ID) as TASK_ID FROM TK_DEMAND D, WF_TASK WT, WF_PROCESS_INSTANCE I ")
			.append(" WHERE WT.TASK_NAME = '").append(taskName).append("' AND D.DEMAND_ID IN (").append(formIds).append(") AND D.DEMAND_ID = I.FORM_ID AND I.FORM_TYPE_ID = '187700' AND I.INSTANCE_ID = WT.INSTANCE_ID ")
			.append(" and WT.OPERATE_TYPE ='1'  group by WT.INSTANCE_ID ");
		Query query = getSession().createSQLQuery(sql.toString());
		
		return query.list();
	}
	
	public Long getNextSequence() {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL ");
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		return ((BigDecimal) query.uniqueResult()).longValue();
	}

	@Override
	public Page<MyTask> findMyTasks(Page<MyTask> page, Long instanceId) {
		Criterion[] criterions = createFilter(instanceId);
		int totalCount = this.findIntByCriteria(criterions);
		page.setTotalCount(totalCount);
		if (0 < totalCount) {
			Criteria c = createCriteria(criterions);
			c = this.setPageParameter(c, page);
			@SuppressWarnings({ "unchecked" })
			List<MyTask> result = c.list();
			page.setDataList(result);
		}
		return page;
	}
	
	private Criterion[] createFilter(Long instanceId) {
		if (null == instanceId) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		criterions.add(Restrictions.eq("instanceId", instanceId));
		return criterions.toArray(new Criterion[criterions.size()]);
	}
}
